﻿CREATE FUNCTION [dbo].[ufnGetAllCategories]()
RETURNS @retCategoryInformation TABLE 
(
[ParentProductCategoryName] [nvarchar](50) NULL, 
[ProductCategoryName] [nvarchar](50) NOT NULL,
[ProductCategoryID] [int] NOT NULL
)
AS 
BEGIN
WITH CategoryCTE([ParentProductCategoryID], [ProductCategoryID], [Name]) AS 
(
SELECT [ParentProductCategoryID], [ProductCategoryID], [Name]
FROM SalesLT.ProductCategory
WHERE ParentProductCategoryID IS NULL
UNION ALL
SELECT C.[ParentProductCategoryID], C.[ProductCategoryID], C.[Name]
FROM SalesLT.ProductCategory AS C
INNER JOIN CategoryCTE AS BC ON BC.ProductCategoryID = C.ParentProductCategoryID
)
INSERT INTO @retCategoryInformation
SELECT PC.[Name] AS [ParentProductCategoryName], CCTE.[Name] as [ProductCategoryName], CCTE.[ProductCategoryID]  
FROM CategoryCTE AS CCTE
JOIN SalesLT.ProductCategory AS PC 
ON PC.[ProductCategoryID] = CCTE.[ParentProductCategoryID];
RETURN;
END;

GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Table value function returning every product category and its parent, if applicable.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'FUNCTION', @level1name = N'ufnGetAllCategories';

